package com.yiran.dao;

import java.sql.*;
import java.util.*;

import com.yiran.model.UserClass;
import com.yiran.util.DBConnection;

public class UserBean {
	private Connection con;
	UserClass user;

	public UserBean() {
		try {
			con = DBConnection.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setUserInfo(UserClass user) {
		this.user = user;
	}

	public void addUserInfo() throws Exception {
		try {
			String sql = "insert into users(usersName,usersPass,usersRepass,usersPassQuestion,usersPassReply,"
					+ "usersTrueName,usersAddress,usersPhone,usersE_mail) values(?,?,?,?,?,?,?,?,?)";
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setString(1, user.getUsersName());
			stm.setString(2, user.getUsersPass());
			stm.setString(6, user.getUsersTrueName());
			stm.setString(7, user.getUsersAddress());
			stm.setString(8, user.getUsersPhone());
			stm.setString(9, user.getUsersE_mail());
			try {
				stm.executeUpdate();
			} catch (Exception e) {
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	public UserClass checkUserInfo(String username, String passw) throws Exception {
		try {

			PreparedStatement stat = con
					.prepareStatement("select * from users where usersName=? and usersPass=?");
			stat.setString(1, username.trim());
			stat.setString(2, passw.trim());
			ResultSet result = stat.executeQuery();

			while (result.next()) {
				UserClass user1 = new UserClass();
				int usersid = result.getInt(1);
				String name = result.getString(2);
				String pass = result.getString(3);
				String truename = result.getString(4);
				String address = result.getString(5);
				String phone = result.getString(6);
				String email = result.getString(7);

				user1.setUsersId(usersid);
				user1.setUsersName(name);
				user1.setUsersPass(pass);
				user1.setUsersTrueName(truename);
				user1.setUsersAddress(address);
				user1.setUsersPhone(phone);
				user1.setUsersE_mail(email);
				return user1;
			}
			result.close();
			stat.close();
			return null;

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	public UserClass getUserInfo() throws Exception {
		UserClass user1 = new UserClass();
		try {
			PreparedStatement stat = con
					.prepareStatement("select * from users where usersName=? and usersPass=?");
			stat.setString(1, user.getUsersName());
			stat.setString(2, user.getUsersPass());
			ResultSet result = stat.executeQuery();

			while (result.next()) {
				int usersid = result.getInt(1);
				String name = result.getString(2);
				String pass = result.getString(3);
				String repass = result.getString(4);
				String question = result.getString(5);
				String reply = result.getString(6);
				String truename = result.getString(7);
				String address = result.getString(8);
				String phone = result.getString(9);
				String email = result.getString(10);

				user1.setUsersId(usersid);
				user1.setUsersName(name);
				user1.setUsersPass(pass);
				user1.setUsersTrueName(truename);
				user1.setUsersAddress(address);
				user1.setUsersPhone(phone);
				user1.setUsersE_mail(email);

			}
			result.close();
			stat.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return user1;
	}

	public Collection getAllUserInfo() {
		UserClass userClass = new UserClass();
		ArrayList arraylist = new ArrayList();
		try {
			Statement statement = con.createStatement();
			ResultSet resultset = statement.executeQuery("select * from users");

			while (resultset.next()) {
				userClass = new UserClass();
				int usersid = resultset.getInt(1);
				String name = resultset.getString(2);
				String pass = resultset.getString(3);
				String truename = resultset.getString(4);
				String address = resultset.getString(5);
				String phone = resultset.getString(6);
				String email = resultset.getString(7);

				//System.out.println(i+" "+s1+" "+s2+" "+s3+" "+s4+" "+s5+" "+s6+" ****1****");
				userClass.setUsersId(usersid);
				userClass.setUsersName(name);
				userClass.setUsersPass(pass);
				userClass.setUsersTrueName(truename);
				userClass.setUsersAddress(address);
				userClass.setUsersPhone(phone);
				userClass.setUsersE_mail(email);
				arraylist.add(userClass);
			}

			con.close();
		}

		catch (Exception e) {
			e.printStackTrace();
		}
		return arraylist;
	}

	public Collection getUserByUserId(String s) {
		int i1 = Integer.parseInt(s);
		UserClass userClass = new UserClass();
		ArrayList arraylist = new ArrayList();
		try {
			PreparedStatement statement = con
					.prepareStatement("select * from users where usersId='"
							+ i1 + "'");
			ResultSet resultset = statement.executeQuery();

			while (resultset.next()) {

				int i = resultset.getInt(1);
				String s1 = resultset.getString(2);
				String s2 = resultset.getString(3);
				String s3 = resultset.getString(4);
				String s4 = resultset.getString(5);
				String s5 = resultset.getString(6);
				String s6 = resultset.getString(7);
				String s7 = resultset.getString(8);
				String s8 = resultset.getString(9);
				String s9 = resultset.getString(10);

				userClass.setUsersId(i);
				userClass.setUsersName(s1);
				userClass.setUsersPass(s2);
				userClass.setUsersTrueName(s6);
				userClass.setUsersAddress(s7);
				userClass.setUsersPhone(s8);
				userClass.setUsersE_mail(s9);

				arraylist.add(userClass);
			}
			resultset.close();
			statement.close();

			con.close();
		}

		catch (Exception e) {
			e.printStackTrace();
		}
		return arraylist;
	}

	public Collection getUserByUserName(String s) {
		UserClass userClass = new UserClass();
		ArrayList arraylist = new ArrayList();
		// System.out.println(s+"*************1");
		try {
			PreparedStatement statement = con
					.prepareStatement("select * from users where usersName='"
							+ s + "'");
			ResultSet resultset = statement.executeQuery();

			while (resultset.next()) {

				int i = resultset.getInt(1);
				String s1 = resultset.getString(2);
				String s2 = resultset.getString(3);
				String s3 = resultset.getString(4);
				String s4 = resultset.getString(5);
				String s5 = resultset.getString(6);
				String s6 = resultset.getString(7);
				String s7 = resultset.getString(8);
				String s8 = resultset.getString(9);
				String s9 = resultset.getString(10);

				userClass.setUsersId(i);
				userClass.setUsersName(s1);
				userClass.setUsersPass(s2);
				userClass.setUsersTrueName(s6);
				userClass.setUsersAddress(s7);
				userClass.setUsersPhone(s8);
				userClass.setUsersE_mail(s9);

				//	System.out.println(s1+"*************2");
				arraylist.add(userClass);
			}
			resultset.close();
			statement.close();

		}

		catch (Exception e) {
			e.printStackTrace();
		}
		return arraylist;
	}

	public void deleteUser(String s) {
		try {
			int i = Integer.parseInt(s);
			//System.out.println(i+"  @@@@@@@@@@@@");
			Statement statement = con.createStatement();
			statement.execute("delete from users where usersId='" + i + "'");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void updateUser(UserClass user){
		try {
			String sql = "UPDATE users SET usersName = '"+ user.getUsersName() + 
			"', usersPass = '"+user.getUsersPass() +"', usersTrueName='"+ 
			user.getUsersTrueName() + "', usersAddress='" + user.getUsersAddress() + "', usersPhone='"
			+ user.getUsersPhone() + "', usersE_mail='" + user.getUsersE_mail() + "' where usersId='"+
			user.getUsersId() + "';";
			System.out.print(sql);
			Statement statement = con.createStatement();
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}
